﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
/// <summary>
/// 
/// </summary>
namespace T4App
{

    public class config
    {
        //修改要生成的数据库表
        public static readonly string ConnectionString = "Password=jhecis;User ID=jhecis;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.103.152)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
        public static readonly string DbDatabase = "Inferno";
        public static readonly string TableName = "RepositoryModel";
    }
    public class TableColumn
    {
        public string COMMENTS { get; set; }
        public string COLUMN_NAME { get; set; }
        public string DATA_TYPE { get; set; }
        public string TTANS { set { DATA_TYPE = MapCommonType(value); } }
        private static string MapCommonType(string type)
        {
            string dbtype = type.ToLower();
            switch (dbtype)
            {
                case "bigint": return "long";
                case "binary": return "byte[]";
                case "bit": return "bool";
                case "char": return "string";
                case "date": return "DateTime";
                case "datetime": return "DateTime";
                case "datetime2": return "DateTime";
                case "datetimeoffset": return "DateTimeOffset";
                case "decimal": return "decimal";
                case "float": return "double";
                case "image": return "byte[]";
                case "int": return "int";
                case "money": return "decimal";
                case "nchar": return "string";
                case "ntext": return "string";
                case "numeric": return "decimal";
                case "nvarchar": return "string";
                case "real": return "Single";
                case "smalldatetime": return "DateTime";
                case "smallint": return "short";
                case "smallmoney": return "decimal";
                case "sql_variant": return "object";
                case "sysname": return "object";
                case "text": return "string";
                case "time": return "TimeSpan";
                case "timestamp": return "byte[]";
                case "tinyint": return "byte"; ;
                case "uniqueidentifier": return "Guid";
                case "varbinary": return "byte[]";
                case "varchar": return "string";
                case "varchar2": return "string";
                case "xml": return "string";
                default: return "object";
            }

        }

    }
    public class DbHelper
    {
        public static string GetParm(List<TableColumn> cols, string Header="")
        {
            List<string> listcol = cols.Select(c => c.COLUMN_NAME).ToList();
            if (!string.IsNullOrEmpty(Header))
            {
                List<string> newlist = new List<string>();
                foreach (var item in listcol)
                {
                    newlist.Add(":" + item);
                }
                listcol = newlist;
            }
            return string.Join(",", listcol);
        }
        public static List<TableColumn> GetColumnsByTableName(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = "DICT_TUBE";
            }
            string sql = $"select  t.COLUMN_NAME,c.COMMENTS ,t.DATA_TYPE,t.NULLABLE from user_tab_columns t,user_col_comments c where t.table_name = c.table_name  and t.column_name = c.column_name and t.TABLE_NAME='{tableName}'";
            List<TableColumn> list = new List<TableColumn>();
            DataTable dt = ExecToSqlGetTable(config.ConnectionString, sql, null);
            if (dt?.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    TableColumn col = new TableColumn() { COLUMN_NAME = dt.Rows[i]["COLUMN_NAME"].ToString(), TTANS = dt.Rows[i]["DATA_TYPE"].ToString(), COMMENTS = dt.Rows[i]["COMMENTS"].ToString() };
                    list.Add(col);
                }
            }
            return list;
        }

        public static List<string> GetDbTables()
        {
            string sql = "SELECT * FROM all_tables t WHERE t.OWNER = 'JHECIS' and t.TABLE_NAME like 'DICT_%'";
            List<string> list = new List<string>();
            try
            {
                DataTable dt = ExecToSqlGetTable(config.ConnectionString, sql, null);
                if (dt?.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        list.Add(dt.Rows[i]["TABLE_NAME"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
            }
            return list;
        }


        public static DataTable ExecToSqlGetTable(string strConnstring, string cmdText, OracleParameter[] oracleParameters)
        {
            using (OracleConnection conn = new OracleConnection(strConnstring))
            {
                OracleCommand cmd = new OracleCommand(cmdText, conn);
                cmd.CommandType = CommandType.Text;
                if (oracleParameters?.Count() > 0)
                {
                    cmd.Parameters.AddRange(oracleParameters);
                }

                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                oda.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
        }

    }
}
